# coding=utf-8

import sys
import datetime

from builtins import int

sys.path.append("..")
from common.mysql.connect import MysqlConnector
from common.redis.connect import RedisConnector
from common.utils.time import get_yesterday_date
from common.utils.time import turn_date_to_ts
from models.manager import get_dict_of_mid_and_bid


def get_assistant_everyday_sale_info(mysql, staff_id, ts_s, ts_e):
    """ 获取销售每日下单情况，包含成交订单数，售出金额
    """
    sql_sale_info = "SELECT 1 type,og.order_id,og.goods_id,og.goods_num,og.goods_num * og.goods_price total," \
                    "oas.id after_sale_id,IFNULL(oasg.refund_num,0) refund_num,\
                    IFNULL(oasg.refund_num * og.goods_price,0) refund_order_amount " \
                    "FROM `order` o LEFT JOIN `order_goods` og ON o.id = og.order_id " \
                    "LEFT JOIN `order_after_sale` oas ON og.order_id = oas.order_id " \
                    "LEFT JOIN `order_after_sale_goods` oasg ON oas.id = oasg.order_after_sale_id " \
                    "WHERE o.staff_id = %d \
                    AND o.verify_at >= '%s' AND o.verify_at <= '%s' \
                    AND og.shipping_status =2 " % (staff_id, ts_s, ts_e)
    sale_res = mysql.select(sql_sale_info)
    return sale_res

def get_inlay_everyday_sale_info(mysql, staff_id, ts_s, ts_e):
    """ 获取客定镶嵌每日下单情况，包含成交订单数，售出金额
    """
    sql_sale_info = "SELECT 2 type,io.id,iog.goods_id,iog.inlay_num,iog.inlay_num * g.business_price total," \
                    "IFNULL(oasg.refund_num,0) refund_num,IFNULL(oasg.refund_num * g.business_price,0) refund_order_amount " \
                    "FROM `inlay_order` io " \
                    "LEFT JOIN `order` o ON io.order_id = o.id " \
                    "LEFT JOIN `inlay_order_goods` iog ON io.id = iog.inlay_order_id " \
                    "LEFT JOIN bojem_microservice_goods.goods g ON iog.goods_id = g.id " \
                    "LEFT JOIN `order_after_sale` oas ON io.order_id = oas.order_id " \
                    "LEFT JOIN `order_after_sale_goods` oasg ON oas.id = oasg.order_after_sale_id " \
                    "LEFT JOIN `inlay_order_pay_info` iopi ON io.id = iopi.inlay_order_id " \
                    "WHERE " \
                    "o.staff_id = %d AND io.inlay_status = 7 AND iog.delivery_id <>0 AND iopi.verify_res=1 " \
                    "AND iopi.verify_at >= '%s' AND iopi.verify_at <= '%s'" % (staff_id, ts_s, ts_e)
    sale_res = mysql.select(sql_sale_info)
    return sale_res


def cal_sale_num_amount(goods_sale_info):
    """ 根据查询结果计算商品当日的订单数量和售出金额
    """
    sale_info_return = {'order_num': 0, 'order_amount': 0.00,'inlay_num':0,
                        'inlay_amount':0.00,'refund_order_num':0,'refund_order_amount':0.00}
    for info in goods_sale_info:
        if info['refund_num'] != 0 :
            sale_info_return['refund_order_num'] += 1
            sale_info_return['refund_order_amount'] += info['refund_order_amount']
        else :
            if info['type'] == 1 :
                sale_info_return['order_num'] += 1
                sale_info_return['order_amount'] += info['total']
            else :
                sale_info_return['inlay_num'] += 1
                sale_info_return['inlay_amount'] += info['inlay_amount']
    return sale_info_return


def goods_sale_info(mysql, staff_id, date):
    """ 助理号每天销售信息汇总
    """
    # 根据时间戳获取当天的开始和结束时间戳，作为基准
    ts_start = turn_date_to_ts(date)
    ts_end = ts_start + 60 * 60 * 24 - 1

    goods_sale_res = get_assistant_everyday_sale_info(mysql, staff_id, ts_start, ts_end)
    goods_inlay_sale_res = get_inlay_everyday_sale_info(mysql, staff_id, ts_start, ts_end)
    goods_sale_res.extend(goods_inlay_sale_res)

    # 获取写数据的数据库连接实例
    mysql_db2 = MysqlConnector(2)

    dict_mid_bid = get_dict_of_mid_and_bid()

    # sql_insert = "insert into goods_sale_everyday_info_%d USING goods_sale_everyday_info \
    #             TAGS (%d) values " % (goods_id, goods_id)
    sql_insert = "insert into salesman (`staff_id`, `date`, `order_num`, `order_amount`,`inlay_num`," \
                 "`inlay_amount`,`refund_order_num`, `refund_order_amount`) values "

    # goods_sale_res = get_goods_everyday_sale_info(mysql, goods_id, ts_start, ts_end)

    if not goods_sale_res:
        # 当天没有售出，td中写入一条空值
        # sql_insert += " (%d, %d, %d, %f, %d, %f) " % (goods_id, ts_start, 0, 0.00, 0, 0.00)
        return True
    else:
        # 当天有售出，汇总售出结果，并写入td
        sale_num_amount = cal_sale_num_amount(goods_sale_res)
        sql_insert += " (%d, %d, %d, %f, %d, %f,%d, %f)  " % \
                      (staff_id, ts_start, sale_num_amount['order_num'], sale_num_amount['order_amount'],
                       sale_num_amount['inlay_num'],sale_num_amount['refund_order_num'],
                       sale_num_amount['refund_order_num'], sale_num_amount['refund_order_amount'])

    # exec_res = taos_db.execute(sql_insert)
    exec_res = mysql_db2.execute(sql_insert)
    if not exec_res:
        return False
    return True


if __name__ == '__main__':
    """ 商品相关数据处理
    """
    # 获取要处理的日期
    if len(sys.argv) == 2:
        # 外部传递进来
        date = sys.argv[1]
    else :
        # 默认取前一天的日期
        date = datetime.datetime.strftime(get_yesterday_date(), "%Y-%m-%d")

    redis_14 = RedisConnector(15)
    mysql_db = MysqlConnector(5)

    while True:
        staff_id_pop = redis_14.rpop('staff_id_list')

        if staff_id_pop and (not redis_14.sismember('staff_list_' + date, staff_id_pop)):
            res = goods_sale_info(mysql_db, int(staff_id_pop), date)
            if res:
                redis_14.sadd('staff_list_' + date, staff_id_pop)
            redis_14.lpush('staff_id_list', staff_id_pop)
        else :
            print(date + "no more data")
            break
